package com.huatai.datadevelop.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.huatai.common.error.exception.ServiceException;
import com.huatai.datadevelop.entity.TableConfigEntity;
import com.huatai.datadevelop.mapper.TableConfigMapper;
import com.huatai.datadevelop.mapper.TableManageMapper;
import com.huatai.datadevelop.utils.JDBCUtils;
import com.huatai.datadevelop.utils.PageUtils;
import com.huatai.datadevelop.utils.Query;
import com.huatai.datadevelop.vo.InfoDataVO;
import com.huatai.datadevelop.vo.TableStructureModel;
import com.huatai.datadevelop.vo.UpdateTableVO;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;


@Service
public class TableConfigService extends ServiceImpl<TableConfigMapper, TableConfigEntity> {

	@Autowired
	private TableManageMapper tableManageMapper;

	@Autowired
	private CommonService commonService;

	@Autowired
	private TableManageService tableManageService;

	@Autowired
	private InterfaceTableConfigService interfaceTableConfigService;


	/**
	 * 根据表名获取数据表清单
	 *
	 * @param tableName
	 * @return
	 */
	public TableConfigEntity getEntityByTableName(String tableName) {
		QueryWrapper<TableConfigEntity> qw = new QueryWrapper<>();
		qw.eq("table_name", tableName);
		TableConfigEntity entity = super.getOne(qw);
		return entity;
	}

	/**
	 * 获取数据表是否合规
	 *
	 * @param tableName
	 * @return
	 */
	public HashMap<String, Object> getTableNameCompliance(String tableName) {
		HashMap<String, Object> hashMap = new HashMap<>();
		hashMap.put("success", true);
		boolean ztb = tableName.startsWith("stic_");
		if (!ztb) {
			hashMap.put("success", false);
			hashMap.put("message", "表名称未以stic_，请调整");
			return hashMap;
		}

		QueryWrapper<TableConfigEntity> qw = new QueryWrapper<>();
		qw.eq("table_name", tableName);
		int count = (int) this.count(qw);
		if (count > 0) {
			hashMap.put("success", false);
			hashMap.put("message", "表名称重复，请调整表名称");
			return hashMap;
		}
		hashMap.put("message", "验证通过");
		return hashMap;
	}

	/**
	 * 分页获取数据
	 *
	 * @param params
	 * @return
	 */
	public PageUtils queryPage(Map<String, Object> params) {
		String name = (String) params.get("name");
		IPage<TableConfigEntity> page = this.page(
			new Query<TableConfigEntity>().getPage(params),
			new QueryWrapper<TableConfigEntity>().like(StringUtils.isNotBlank(name), "name", name)
		);
		page.getRecords().stream().forEach(p -> {
			String tableModel = p.getTableModel();
			List<TableStructureModel> tableStructureModels = JSON.parseArray(tableModel, TableStructureModel.class);
			p.setTableList(tableStructureModels);
		});
		return new PageUtils(page);
	}

	/**
	 * 删除数据
	 *
	 * @param ids
	 */
	@Transactional(rollbackFor = Exception.class)
	public Boolean deleteByIds(List<String> ids) {
		final Boolean[] aBoolean = {true};
		ids.forEach(id -> {
			TableConfigEntity byId = this.getById(id);
			String tableName = byId.getTableName();
			//TODO 此处将相关的表删除  表存在关联关系
			this.exitTableRelation(tableName);
			this.tableManageMapper.deleteTable(tableName);
			aBoolean[0] = super.removeById(id);
		});
		return aBoolean[0];
	}

	/**
	 * 新增或者更新数据
	 *
	 * @param entity
	 * @return
	 */
	@Transactional(rollbackFor = Exception.class)
	public Boolean saveOrUpdateEntity(TableConfigEntity entity) throws SQLException {
		//TODO 查看数据表是否存在，库表里面是否有数据，无数据则删除表格，新建
		Boolean aBoolean = this.tableManageService.exitTable(entity.getTableName());
		if (aBoolean) {
			//若无数据，则把数据库表删除
			this.exitTableRelation(entity.getTableName());
			this.tableManageMapper.deleteTable(entity.getTableName());
		}

		//拼接创建sql 语句
		List<TableStructureModel> tableList = entity.getTableList();
		StringBuilder sb = new StringBuilder();
		if (tableList != null && tableList.size() > 0) {
			for (int i = 0; i < tableList.size(); i++) {
				TableStructureModel tableStructureModel = tableList.get(i);
				String name = tableStructureModel.getName();
				if (commonService.isNumber(name)) {
					throw new ServiceException("库表字段配置第" + (i + 1) + "行的列名是数字，暂不支持数字列名");
				}
				if (StringUtils.isNotBlank(name)) {
					sb = sb.append(tableStructureModel.getName()).append(" ")
						.append(tableStructureModel.getType()).append("(")
						.append(tableStructureModel.getLength()).append(")")
						.append(" COMMENT '")
						.append(tableStructureModel.getNote())
						.append("', ");
				}
			}
			String collect = tableList.stream().map(p -> p.getName()).collect(Collectors.joining(","));
			collect = "autoId," + collect;
			entity.setField(collect);
			entity.setTableModel(JSON.toJSONString(entity.getTableList()));
		}
		String sql = " CREATE TABLE " + entity.getTableName();
		if (StringUtils.isNotBlank(sb.toString())) {
			sql = sql + " ( autoId INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT '主键id', " + sb.toString().substring(0, sb.toString().length() - 2) + ")";
		} else {
			sql = sql + " ( autoId INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT '主键id') ";
		}

		//建立表注释
		sql = sql + " COMMENT = '" + entity.getName() + "'";

		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtils.getConnection();
			statement = connection.prepareStatement(sql);
			statement.execute();
			return super.saveOrUpdate(entity);
		} catch (Exception e) {
			log.error("新增或更新报错：" + e.getMessage());
			connection.rollback();
		} finally {
			JDBCUtils.close(statement, connection);
		}
		return false;
	}

	/**
	 * 根据表名获取该表数据
	 *
	 * @param params
	 * @return
	 */
	public InfoDataVO getDataListById(Map<String, Object> params) {
		Object id = params.get("id");
		InfoDataVO vo = new InfoDataVO();
		if (id != null) {
			TableConfigEntity entity = this.getById(Integer.valueOf(id.toString()));
			String tableName = entity.getTableName();
			HashMap<String, Object> hashMap = new HashMap<>();
			JSONObject dataCountByTableName = this.baseMapper.getDataCountByTableName(tableName);
			Long dataCount = dataCountByTableName.getLong("dataCount");
			hashMap.put("dataCount", dataCount);
			String field = entity.getField();

			Integer page = Integer.parseInt(params.get("page").toString());
			Integer limit = Integer.parseInt(params.get("limit").toString());
			String limitStr = " limit " + (page - 1) * limit + " , " + limit + ";";

			List<HashMap<String, Object>> dataListById = this.baseMapper.getDataListById(field, tableName, limitStr);

			//遍历数据，将数据为null的改为空值
			dataListById.stream().forEach(p -> {
				for (Map.Entry<String, Object> entry : p.entrySet()) {
					Object value = entry.getValue();
					if (value == null || value.toString().equalsIgnoreCase("null")) {
						p.put(entry.getKey(), "");
					}
				}
			});

			hashMap.put("dataList", dataListById);
			vo.setDataInfo(hashMap);
			vo.setTitleList(Arrays.asList(field.split(",")));
		}
		return vo;
	}

	/**
	 * 批量更新数据
	 *
	 * @param vo
	 */
	@Transactional(rollbackFor = Exception.class)
	public void saveOrUpdataBatchByTableName(UpdateTableVO vo) {
		Integer id = vo.getId();
		TableConfigEntity entity = this.getById(id);
		List<HashMap<String, Object>> dataList = vo.getDataList();
		if (dataList != null && dataList.size() > 0) {
			String tableName = entity.getTableName();
			for (int i = 0; i < dataList.size(); i++) {
				StringBuilder file = new StringBuilder();
				StringBuilder insertValue = new StringBuilder();
				StringBuilder updateValue = new StringBuilder();
				HashMap<String, Object> hashMap = dataList.get(i);

				//TODO 针对数据转换调整
				Object autoIdParam = hashMap.get("autoId");
				if (autoIdParam != null && StringUtils.isEmpty(autoIdParam.toString())) {
					hashMap.remove("autoId");
				}

				insertValue.append("(");
				for (Map.Entry<String, Object> stringStringEntry : hashMap.entrySet()) {
					String key = stringStringEntry.getKey();
					Object value = stringStringEntry.getValue();
					if (value == null || value.toString().equalsIgnoreCase("null")) {
						value = "";
					}
					file.append(key).append(",");
					insertValue.append("'").append(value).append("',");
					updateValue.append(" ").append(key).append("='").append(value).append("',");
				}
				Object autoId = hashMap.get("autoId");
				//新增   ('李四4', 'T恤衫', '衣服'),('李四5', 'T恤衫', '衣服'),
				if (autoId == null) {
					String insert = insertValue.toString().substring(0, insertValue.toString().length() - 1);
					String fileStr = file.toString().substring(0, file.toString().length() - 1);
					this.baseMapper.insertData(tableName, fileStr, insert + ")");
				}
				//更新数据  name='张武' ,name='张武'
				else {
					String update = updateValue.toString().substring(0, updateValue.toString().length() - 1);
					this.baseMapper.updateData(tableName, update, "autoId = " + autoId);
				}
			}
		}
	}

	/**
	 * 判断表中是否存在关联关系
	 *
	 * @param tableName
	 */
	private void exitTableRelation(String tableName) {
		Integer count = this.tableManageMapper.countData(tableName);
		if (count > 0) {
			throw new ServiceException("数据表" + tableName + "已经存在数据，无法更新");
		}
	}

	/**
	 * 根据表id一键清空数据
	 *
	 * @param data
	 */
	public Boolean deleteAllDataByTableId(JSONObject data) {
		Boolean aBoolean = true;
		JSONArray idList = data.getJSONArray("idList");
		TableConfigEntity entity = this.getById(data.getString("id"));
		//有选择数据时 删除选择的数据  否则清空整张表
		if (idList != null && idList.size() > 0) {
			String s = JSON.toJSONString(idList).replace("[", "").replace("]", "");
			String whereStr = " where autoId in(" + s + ")";
			aBoolean = this.tableManageMapper.deleteAllDataByTableName(entity.getTableName(), whereStr);
		} else {
			aBoolean = this.tableManageMapper.deleteAllDataByTableName(entity.getTableName(), "");
		}
		return aBoolean;
	}
}
